package com.dudu.dao;

import java.sql.Connection;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import core.CoreDao;
import core.db.DBConnectionUtil;
import net.sf.json.JSONObject;

/**
 * 日报表业务Dao类
 * 
 * @author jack
 * 
 */
public class DailyReportDao {

    public static Logger logger = Logger.getLogger(DailyReportDao.class);

    /**
     * 应用消耗量日统计
     * 
     * @param dao
     * @param appKey
     * @param beginTime
     * @param endTime
     * @return
     */
    public List<JSONObject> queryByAppAndTime(CoreDao dao, String appKey, String beginTime, String endTime) {
	Connection con = null;
	List<JSONObject> list = null;
	try {
	    if (StringUtils.isNotBlank(appKey) && StringUtils.isNotBlank(beginTime) && StringUtils.isNotBlank(endTime)) {
		con = DBConnectionUtil.getDBConnection("emate2");
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer.append(" SELECT t.* from (");
		stringBuffer.append(" SELECT ");
		stringBuffer.append(" 	c.product_code, REPLACE(FORMAT(COUNT(1), 0), ',', '') AS 'request_count', "
			+ " REPLACE(FORMAT(IFNULL(SUM(c.amount), 0), 0), ',', '') AS 'total_time',  ");
		stringBuffer.append(" 	CASE  ");
		stringBuffer.append(" 		WHEN c.unit='MIN' THEN '分钟'  ");
		stringBuffer.append(" 		WHEN c.unit='QUANTITY' THEN '条'  ");
		stringBuffer.append(" 		ELSE c.unit  ");
		stringBuffer.append(" 	END AS 'unit' ");
		stringBuffer.append(" FROM  ");
		stringBuffer.append(" 	communication_info c  ");
		stringBuffer.append(" WHERE ");
		stringBuffer.append(" 	c.app_key=? ");
		stringBuffer.append(" 	AND c.request_time >=? AND c.request_time <=? ");
		stringBuffer.append(" GROUP BY c.product_code )t ");
		stringBuffer.append(" ORDER BY t.total_time DESC; ");

		list = dao.serviceJsonSql(con, stringBuffer.toString(), new Object[] { appKey, beginTime, endTime });
	    }
	} catch (Exception ex) {
	    logger.error("queryByAppAndTime error:" + ex.getMessage());
	    ex.printStackTrace();
	} finally {
	    DBConnectionUtil.close(null, null, con);
	}
	return list;
    }

    /**
     * 通道消耗统计
     * 
     * @param dao
     * @param beginTime
     * @param endTime
     * @return
     */
    public List<JSONObject> queryProductByTime(CoreDao dao, String beginTime, String endTime) {
	Connection con = null;
	List<JSONObject> list = null;
	try {
	    if (StringUtils.isNotBlank(beginTime) && StringUtils.isNotBlank(endTime)) {
		con = DBConnectionUtil.getDBConnection("emate2");
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer.append(" SELECT t.* from (");
		stringBuffer.append(" SELECT ");
		stringBuffer.append(" 	c.product_subcode, REPLACE(FORMAT(COUNT(1), 0), ',', '') AS 'request_count', "
			+ " REPLACE(FORMAT(IFNULL(SUM(c.amount), 0), 0), ',', '') AS 'total_time',  ");
		stringBuffer.append(" 	CASE  ");
		stringBuffer.append(" 		WHEN c.unit='MIN' THEN '分钟'  ");
		stringBuffer.append(" 		WHEN c.unit='QUANTITY' THEN '条'  ");
		stringBuffer.append(" 		ELSE c.unit  ");
		stringBuffer.append(" 	END AS 'unit' ");
		stringBuffer.append(" FROM  ");
		stringBuffer.append(" 	communication_info c  ");
		stringBuffer.append(" WHERE ");
		stringBuffer.append("   c.request_time >=? AND c.request_time <=? ");
		stringBuffer.append(" GROUP BY c.product_subcode )t ");
		stringBuffer.append(" ORDER BY t.total_time DESC; ");

		list = dao.serviceJsonSql(con, stringBuffer.toString(), new Object[] { beginTime, endTime });
	    }
	} catch (Exception ex) {
	    logger.error("queryProductByTime error:" + ex.getMessage());
	    ex.printStackTrace();
	} finally {
	    DBConnectionUtil.close(null, null, con);
	}
	return list;
    }

    /**
     * 查询被叫超过1人的多方通话
     * 
     * @param dao
     * @param beginTime
     * @param endTime
     * @return
     */
    public List<JSONObject> queryCalledMoreThanOneByTime(CoreDao dao, String beginTime, String endTime) {
	Connection con = null;
	List<JSONObject> list = null;
	try {
	    if (StringUtils.isNotBlank(beginTime) && StringUtils.isNotBlank(endTime)) {
		con = DBConnectionUtil.getDBConnection("emate2");
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer.append(" SELECT COUNT(1) AS amount, " + " REPLACE(FORMAT(IFNULL(SUM(c.amount), 0.0), 0), ',', '') AS 'total_time' ");
		stringBuffer.append(" FROM  ");
		stringBuffer.append(" 	communication_info c  ");
		stringBuffer.append(" WHERE ");
		stringBuffer.append("   c.request_time >=? AND c.request_time <=? ");
		stringBuffer.append("   AND c.product_subcode = 'TalksV2' ");
		stringBuffer.append("   AND c.called LIKE '%,%' ");

		list = dao.serviceJsonSql(con, stringBuffer.toString(), new Object[] { beginTime, endTime });
	    }
	} catch (Exception ex) {
	    logger.error("queryProductByTime error:" + ex.getMessage());
	    ex.printStackTrace();
	} finally {
	    DBConnectionUtil.close(null, null, con);
	}
	return list;
    }

    /**
     * 活跃数日统计
     * 
     * @param dao
     * @param appKey
     * @param beginTime
     * @param endTime
     * @return
     */
    public List<JSONObject> queryActiveByAppAndTime(CoreDao dao, String appKey, String beginTime, String endTime) {
	Connection con = null;
	List<JSONObject> list = null;
	try {
	    if (StringUtils.isNotBlank(appKey) && StringUtils.isNotBlank(beginTime) && StringUtils.isNotBlank(endTime)) {
		con = DBConnectionUtil.getDBConnection("emate2");
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer.append(" SELECT t.* from (");
		stringBuffer.append(" SELECT ");
		stringBuffer.append(" 	c.product_code, ");
		stringBuffer.append(" 	REPLACE(FORMAT(COUNT(DISTINCT(c.cust_account)), 0), ',', '') AS 'activeCompany', ");
		stringBuffer.append(" 	REPLACE(FORMAT(COUNT(DISTINCT(c.ext_terminalCode)), 0), ',', '') AS 'activeUser' ");
		stringBuffer.append(" FROM  ");
		stringBuffer.append(" 	communication_info c  ");
		stringBuffer.append(" WHERE ");
		stringBuffer.append(" 	c.app_key=? ");
		stringBuffer.append(" 	AND c.request_time >=? AND c.request_time <=? ");
		stringBuffer.append(" GROUP BY c.product_code )t ");
		stringBuffer.append(" ORDER BY t.activeUser DESC; ");

		list = dao.serviceJsonSql(con, stringBuffer.toString(), new Object[] { appKey, beginTime, endTime });
	    }
	} catch (Exception ex) {
	    logger.error("queryActiveByAppAndTime error:" + ex.getMessage());
	    ex.printStackTrace();
	} finally {
	    DBConnectionUtil.close(null, null, con);
	}
	return list;
    }

    /**
     * 查询请求次数
     * 
     * @param dao
     * @param appKey
     * @param productCode
     * @param productSubCode
     * @param beginTime
     * @param endTime
     * @return
     */
    public int queryRequestTime(CoreDao dao, String appKey, String productCode, String productSubCode, String beginTime, String endTime) {
	Connection con = null;
	List<JSONObject> list = null;
	int result = 0;
	try {
	    if (StringUtils.isNotBlank(beginTime) && StringUtils.isNotBlank(endTime)) {
		con = DBConnectionUtil.getDBConnection("emate2");
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer.append(" SELECT ");
		stringBuffer.append(" 	COUNT(d.id) AS 'amount' ");
		stringBuffer.append(" FROM  ");
		stringBuffer.append(" 	communication_info i, communication_detail d ");
		stringBuffer.append(" WHERE ");
		stringBuffer.append(" 	i.id=d.info_id ");
		stringBuffer.append(" 	AND i.request_time >=? AND i.request_time <=? ");
		if (StringUtils.isNotBlank(appKey))
		    stringBuffer.append(" 	AND i.app_key = '" + appKey + "' ");
		if (StringUtils.isNotBlank(productCode))
		    stringBuffer.append("  AND i.product_code = '" + productCode + "' ");
		if (StringUtils.isNotBlank(productSubCode))
		    stringBuffer.append("  AND i.product_subcode = '" + productSubCode + "' ");

		list = dao.serviceJsonSql(con, stringBuffer.toString(), new Object[] { beginTime, endTime });
		if (list != null && list.size() > 0) {
		    JSONObject obj = list.get(0);
		    result = obj.optInt("amount");
		}
	    }
	} catch (Exception ex) {
	    logger.error("queryRequestTime error:" + ex.getMessage());
	    ex.printStackTrace();
	} finally {
	    DBConnectionUtil.close(null, null, con);
	}
	return result;
    }
    /**
     * 查询活跃应用
     * @param dao
     * @param beginTime
     * @param endTime
     * @return
     */
    public List<JSONObject> queryActiveAppKeyByTime(CoreDao dao,String beginTime, String endTime,String appkey){
	Connection con = null;
	List<JSONObject> list = null;
	try {
	    if (StringUtils.isNotBlank(beginTime) && StringUtils.isNotBlank(endTime)) {
		con = DBConnectionUtil.getDBConnection("emate1");
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer.append(" SELECT ");
		stringBuffer.append(" 	DISTINCT(i.app_key) ");
		stringBuffer.append(" FROM ");
		stringBuffer.append(" 	t_meet_info i ");
		stringBuffer.append(" WHERE  ");
		stringBuffer.append(" i.app_key != ? ");
		stringBuffer.append(" AND i.create_time >= ? ");
		stringBuffer.append(" AND i.create_time <= ? ");
		stringBuffer.append(" UNION ");
		stringBuffer.append(" SELECT ");
		stringBuffer.append(" 	DISTINCT(s.app_key) ");
		stringBuffer.append(" FROM ");
		stringBuffer.append(" 	t_sms_info s ");
		stringBuffer.append(" WHERE ");
		stringBuffer.append("  s.app_key != ? ");
		stringBuffer.append(" AND s.create_time >= ? ");
		stringBuffer.append(" AND s.create_time <= ? ");

		list = dao.serviceJsonSql(con, stringBuffer.toString(), new Object[] {appkey,beginTime, endTime,appkey,beginTime, endTime });
	    }
	} catch (Exception ex) {
	    logger.error("queryActiveAppKeyByTime error:" + ex.getMessage());
	    ex.printStackTrace();
	} finally {
	    DBConnectionUtil.close(null, null, con);
	}
	return list;
    }
    /**
     * 查询活跃客户
     * 
     * @param dao
     * @param appKey
     * @param beginTime
     * @param endTime
     * @return
     */
    public List<JSONObject> queryActiveCustaccountByAppAndTime(CoreDao dao, String appKey, String beginTime, String endTime) {
	Connection con = null;
	List<JSONObject> list = null;
	try {
	    if (StringUtils.isNotBlank(appKey) && StringUtils.isNotBlank(beginTime) && StringUtils.isNotBlank(endTime)) {
		con = DBConnectionUtil.getDBConnection("emate1");
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer.append(" SELECT ");
		stringBuffer.append(" 	DISTINCT(i.cust_account) ");
		stringBuffer.append(" FROM ");
		stringBuffer.append(" 	t_meet_info i ");
		stringBuffer.append(" WHERE  ");
		stringBuffer.append(" 	i.app_key = ? ");
		stringBuffer.append(" AND i.duration > 0 ");
		stringBuffer.append(" AND i.create_time >= ? ");
		stringBuffer.append(" AND i.create_time <= ? ");
		stringBuffer.append(" UNION ");
		stringBuffer.append(" SELECT ");
		stringBuffer.append(" 	DISTINCT(s.cust_account) ");
		stringBuffer.append(" FROM ");
		stringBuffer.append(" 	t_sms_info_detail s ");
		stringBuffer.append(" WHERE ");
		stringBuffer.append(" 	s.app_key = ? ");
		stringBuffer.append(" AND s.status = '0' ");
		stringBuffer.append(" AND s.create_time >= ? ");
		stringBuffer.append(" AND s.create_time <= ? ");

		list = dao.serviceJsonSql(con, stringBuffer.toString(), new Object[] { appKey, beginTime, endTime, appKey, beginTime, endTime });
	    }
	} catch (Exception ex) {
	    logger.error("queryActiveCustaccountByAppAndTime error:" + ex.getMessage());
	    ex.printStackTrace();
	} finally {
	    DBConnectionUtil.close(null, null, con);
	}
	return list;
    }

    /**
     * 查询企业的消费数据
     * 
     * @param dao
     * @param custAccount
     * @param beginTime
     * @param endTime
     * @return
     */
    public List<JSONObject> queryTotalTimeByCustaccountAndTime(CoreDao dao, String custAccount, String beginTime, String endTime) {
	Connection con = null;
	List<JSONObject> list = null;
	try {
	    if (StringUtils.isNotBlank(custAccount) && StringUtils.isNotBlank(beginTime) && StringUtils.isNotBlank(endTime)) {
		con = DBConnectionUtil.getDBConnection("emate1");
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer.append(" SELECT t2.cust_account, t2.ability_type, SUM(t2.total_time) AS 'total_time', t2.unit FROM ( ");
		stringBuffer.append(" SELECT ");
		stringBuffer.append(" 	s.cust_account, ");
		stringBuffer.append(" 	CASE ");
		stringBuffer.append(" 		WHEN s.ability_type LIKE '%ADSMS%' THEN 'ADSMMS' ");
		stringBuffer.append(" 		ELSE 'SMMS' ");
		stringBuffer.append(" 	END AS ability_type, ");
		stringBuffer.append(" 	COUNT(s.id) AS 'total_time', ");
		stringBuffer.append(" 	'条' AS 'unit' ");
		stringBuffer.append(" FROM ");
		stringBuffer.append(" 	t_sms_info_detail s ");
		stringBuffer.append(" WHERE  ");
		stringBuffer.append(" 	s.cust_account = ? ");
		stringBuffer.append(" 	AND s.create_time >= ? ");
		stringBuffer.append(" 	AND s.create_time <= ? ");
		stringBuffer.append(" 	AND s.`status` = '0' ");
		stringBuffer.append(" GROUP BY ");
		stringBuffer.append(" 	s.ability_type ");
		stringBuffer.append(" ) t2 GROUP BY t2.ability_type ");

		stringBuffer.append(" UNION ");

		stringBuffer.append(" SELECT t.cust_account, t.ability_type, SUM(t.total_time) AS 'total_time', t.unit FROM ( ");
		stringBuffer.append(" SELECT ");
		stringBuffer.append(" 	i.cust_account, ");
		stringBuffer.append(" 	CASE ");
		stringBuffer.append(" 		WHEN i.ability_type LIKE '%70%' THEN 'VOICE70' ");
		stringBuffer.append(" 		WHEN i.ability_type LIKE '%140%' THEN 'VOICE140' ");
		stringBuffer.append(" 		WHEN i.ability_type LIKE '%Dial%' THEN 'DIAL' ");
		stringBuffer.append(" 		WHEN i.ability_type LIKE '%Voice%' THEN 'VOICE' ");
		stringBuffer.append(" 		WHEN i.ability_type LIKE '%Audeo%' THEN 'VOICE' ");
		stringBuffer.append(" 		WHEN i.ability_type LIKE '%Ivr%' THEN 'IVVR' ");
		stringBuffer.append(" 		WHEN i.ability_type LIKE '%TalksV2%' THEN 'MPTY' ");
		stringBuffer.append(" 		WHEN i.ability_type LIKE '%Vcaptcha%' THEN 'VCAPTCHA' ");
		stringBuffer.append(" 		ELSE i.ability_type ");
		stringBuffer.append(" 	END AS ability_type, ");
		stringBuffer.append(" 	SUM(i.duration) AS 'total_time', ");
		stringBuffer.append(" 	'分钟' AS 'unit' ");
		stringBuffer.append(" FROM ");
		stringBuffer.append(" 	t_meet_info i ");
		stringBuffer.append(" WHERE  ");
		stringBuffer.append(" 	i.cust_account = ? ");
		stringBuffer.append(" 	AND i.create_time >= ? ");
		stringBuffer.append(" 	AND i.create_time <= ? ");
		stringBuffer.append(" GROUP BY ");
		stringBuffer.append(" 	i.ability_type ");
		stringBuffer.append(" ) t GROUP BY t.ability_type order by cust_account");

		list = dao.serviceJsonSql(con, stringBuffer.toString(), new Object[] { custAccount, beginTime, endTime, custAccount, beginTime, endTime });
	    }
	} catch (Exception ex) {
	    logger.error("queryTotalTimeByCustaccountAndTime error:" + ex.getMessage());
	    ex.printStackTrace();
	} finally {
	    DBConnectionUtil.close(null, null, con);
	}
	return list;
    }

    /**
     * 短信-应用日消耗查询
     * 
     * @param dao
     * @param appKey
     * @param beginTime
     * @param endTime
     * @return
     */
    public List<JSONObject> queryByAppAndTimeSMS(CoreDao dao, String appKey, String beginTime, String endTime) {
	Connection con = null;
	List<JSONObject> list = null;
	try {
	    if (StringUtils.isNotBlank(appKey) && StringUtils.isNotBlank(beginTime) && StringUtils.isNotBlank(endTime)) {
		con = DBConnectionUtil.getDBConnection("emate1");
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer
			.append(" SELECT t.app_key, t.product_code, SUM(t.total_count) AS 'total_count', SUM(t.success_count) AS 'success_count', SUM(t.fail_count) AS 'fail_count', SUM(t.consume_count) AS 'consume_count' FROM ");
		stringBuffer.append(" ( ");
		stringBuffer.append(" SELECT ");
		stringBuffer.append(" 	s.app_key, ");
		stringBuffer.append(" 	CASE ");
		stringBuffer.append(" 		WHEN s.ability_type LIKE '%ADSMS%' THEN 'ADSMMS' ");
		stringBuffer.append(" 		ELSE 'SMMS' ");
		stringBuffer.append(" 	END AS 'product_code', ");
		stringBuffer.append(" 	COUNT(s.id) AS 'total_count', ");
		stringBuffer.append(" 	( ");
		stringBuffer.append(" 		SELECT ");
		stringBuffer.append(" 			COUNT(DISTINCT(t1.session_id)) ");
		stringBuffer.append(" 		FROM ");
		stringBuffer.append(" 			t_sms_info t1, t_sms_info_detail t2 ");
		stringBuffer.append(" 		WHERE ");
		stringBuffer.append(" 			t1.session_id = t2.session_id ");
		stringBuffer.append(" 		AND t1.app_key = ? ");
		stringBuffer.append(" 		AND t1.create_time >= ? ");
		stringBuffer.append(" 		AND t1.create_time <= ? ");
		stringBuffer.append(" 		AND t1.ability_type = s.ability_type ");
		stringBuffer.append(" 		AND t2.`status` = '0' ");
		stringBuffer.append(" 	) AS 'success_count', ");
		stringBuffer.append(" 	( ");
		stringBuffer.append(" 		SELECT ");
		stringBuffer.append(" 			COUNT(DISTINCT(t1.session_id)) ");
		stringBuffer.append(" 		FROM ");
		stringBuffer.append(" 			t_sms_info t1, t_sms_info_detail t2 ");
		stringBuffer.append(" 		WHERE ");
		stringBuffer.append(" 			t1.session_id = t2.session_id ");
		stringBuffer.append(" 		AND t1.app_key = ? ");
		stringBuffer.append(" 		AND t1.create_time >= ? ");
		stringBuffer.append(" 		AND t1.create_time <= ? ");
		stringBuffer.append(" 		AND t1.ability_type = s.ability_type ");
		stringBuffer.append(" 		AND t2.`status` != '0' ");
		stringBuffer.append(" 	) AS 'fail_count', ");
		stringBuffer.append(" 	( ");
		stringBuffer.append(" 		SELECT ");
		stringBuffer.append(" 			COUNT(DISTINCT(t2.id)) ");
		stringBuffer.append(" 		FROM ");
		stringBuffer.append(" 			t_sms_info t1, t_sms_info_detail t2 ");
		stringBuffer.append(" 		WHERE ");
		stringBuffer.append(" 			t1.session_id = t2.session_id ");
		stringBuffer.append(" 		AND t1.app_key = ? ");
		stringBuffer.append(" 		AND t1.create_time >= ? ");
		stringBuffer.append(" 		AND t1.create_time <= ? ");
		stringBuffer.append(" 		AND t1.ability_type = s.ability_type ");
		stringBuffer.append(" 		AND t2.`status` = '0' ");
		stringBuffer.append(" 	) AS 'consume_count' ");
		stringBuffer.append(" FROM ");
		stringBuffer.append(" 	t_sms_info s ");
		stringBuffer.append(" WHERE ");
		stringBuffer.append(" 	s.app_key = ? ");
		stringBuffer.append(" AND s.create_time >= ? ");
		stringBuffer.append(" AND s.create_time <= ? ");
		stringBuffer.append(" GROUP BY s.ability_type ");
		stringBuffer.append(" )t ");
		stringBuffer.append(" GROUP BY product_code ");

		list = dao.serviceJsonSql(con, stringBuffer.toString(), new Object[] { appKey, beginTime, endTime, appKey, beginTime, endTime, appKey, beginTime, endTime, appKey,
			beginTime, endTime });
	    }
	} catch (Exception ex) {
	    logger.error("queryByAppAndTime error:" + ex.getMessage());
	    ex.printStackTrace();
	} finally {
	    DBConnectionUtil.close(null, null, con);
	}
	return list;
    }

    /**
     * 短信日活跃客户TOP
     * 
     * @param dao
     * @param beginTime
     * @param endTime
     * @param top
     * @return
     */
    public List<JSONObject> queryCustaccountSMS(CoreDao dao, String beginTime, String endTime, int top) {
	Connection con = null;
	List<JSONObject> list = null;
	try {
	    if (StringUtils.isNotBlank(beginTime) && StringUtils.isNotBlank(endTime)) {
		con = DBConnectionUtil.getDBConnection("emate1");
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer.append(" SELECT ");
		stringBuffer.append(" 	s.app_key, ");
		stringBuffer.append(" 	s.cust_account ");
		stringBuffer.append(" FROM ");
		stringBuffer.append(" 	t_sms_info s ");
		stringBuffer.append(" WHERE ");
		stringBuffer.append(" 	s.create_time >= ? ");
		stringBuffer.append(" 	AND s.create_time <= ? ");
		stringBuffer.append(" GROUP BY s.cust_account ");
		stringBuffer.append(" ORDER BY COUNT(id) DESC ");
		stringBuffer.append(" LIMIT ? ");

		list = dao.serviceJsonSql(con, stringBuffer.toString(), new Object[] { beginTime, endTime, top });
	    }
	} catch (Exception ex) {
	    logger.error("queryByAppAndTime error:" + ex.getMessage());
	    ex.printStackTrace();
	} finally {
	    DBConnectionUtil.close(null, null, con);
	}
	return list;
    }

    /**
     * 计费账号-短信日消耗
     * 
     * @param dao
     * @param custAccount
     * @param beginTime
     * @param endTime
     * @return
     */
    public List<JSONObject> queryByCustaccountAndTimeSMS(CoreDao dao, String custAccount, String beginTime, String endTime) {
	Connection con = null;
	List<JSONObject> list = null;
	try {
	    if (StringUtils.isNotBlank(custAccount) && StringUtils.isNotBlank(beginTime) && StringUtils.isNotBlank(endTime)) {
		con = DBConnectionUtil.getDBConnection("emate1");
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer
			.append(" SELECT t.cust_account, t.product_code, SUM(t.total_count) AS 'total_count', SUM(t.success_count) AS 'success_count', SUM(t.fail_count) AS 'fail_count', SUM(t.consume_count) AS 'consume_count' FROM ");
		stringBuffer.append(" ( ");
		stringBuffer.append(" SELECT ");
		stringBuffer.append(" 	s.cust_account, ");
		stringBuffer.append(" 	CASE ");
		stringBuffer.append(" 		WHEN s.ability_type LIKE '%ADSMS%' THEN 'ADSMMS' ");
		stringBuffer.append(" 		ELSE 'SMMS' ");
		stringBuffer.append(" 	END AS 'product_code', ");
		stringBuffer.append(" 	COUNT(s.id) AS 'total_count', ");
		stringBuffer.append(" 	( ");
		stringBuffer.append(" 		SELECT ");
		stringBuffer.append(" 			COUNT(DISTINCT(t1.session_id)) ");
		stringBuffer.append(" 		FROM ");
		stringBuffer.append(" 			t_sms_info t1, t_sms_info_detail t2 ");
		stringBuffer.append(" 		WHERE ");
		stringBuffer.append(" 			t1.session_id = t2.session_id ");
		stringBuffer.append(" 		AND t1.cust_account = ? ");
		stringBuffer.append(" 		AND t1.create_time >= ? ");
		stringBuffer.append(" 		AND t1.create_time <= ? ");
		stringBuffer.append(" 		AND t1.ability_type = s.ability_type ");
		stringBuffer.append(" 		AND t2.`status` = '0' ");
		stringBuffer.append(" 	) AS 'success_count', ");
		stringBuffer.append(" 	( ");
		stringBuffer.append(" 		SELECT ");
		stringBuffer.append(" 			COUNT(DISTINCT(t1.session_id)) ");
		stringBuffer.append(" 		FROM ");
		stringBuffer.append(" 			t_sms_info t1, t_sms_info_detail t2 ");
		stringBuffer.append(" 		WHERE ");
		stringBuffer.append(" 			t1.session_id = t2.session_id ");
		stringBuffer.append(" 		AND t1.cust_account = ? ");
		stringBuffer.append(" 		AND t1.create_time >= ? ");
		stringBuffer.append(" 		AND t1.create_time <= ? ");
		stringBuffer.append(" 		AND t1.ability_type = s.ability_type ");
		stringBuffer.append(" 		AND t2.`status` != '0' ");
		stringBuffer.append(" 	) AS 'fail_count', ");
		stringBuffer.append(" 	( ");
		stringBuffer.append(" 		SELECT ");
		stringBuffer.append(" 			COUNT(DISTINCT(t2.id)) ");
		stringBuffer.append(" 		FROM ");
		stringBuffer.append(" 			t_sms_info t1, t_sms_info_detail t2 ");
		stringBuffer.append(" 		WHERE ");
		stringBuffer.append(" 			t1.session_id = t2.session_id ");
		stringBuffer.append(" 		AND t1.cust_account = ? ");
		stringBuffer.append(" 		AND t1.create_time >= ? ");
		stringBuffer.append(" 		AND t1.create_time <= ? ");
		stringBuffer.append(" 		AND t1.ability_type = s.ability_type ");
		stringBuffer.append(" 		AND t2.`status` = '0' ");
		stringBuffer.append(" 	) AS 'consume_count' ");
		stringBuffer.append(" FROM ");
		stringBuffer.append(" 	t_sms_info s ");
		stringBuffer.append(" WHERE ");
		stringBuffer.append(" 	s.cust_account = ? ");
		stringBuffer.append(" AND s.create_time >= ? ");
		stringBuffer.append(" AND s.create_time <= ? ");
		stringBuffer.append(" GROUP BY s.ability_type ");
		stringBuffer.append(" )t ");
		stringBuffer.append(" GROUP BY product_code ");

		list = dao.serviceJsonSql(con, stringBuffer.toString(), new Object[] { custAccount, beginTime, endTime, custAccount, beginTime, endTime, custAccount, beginTime,
			endTime, custAccount, beginTime, endTime });
	    }
	} catch (Exception ex) {
	    logger.error("queryByCustaccountAndTimeSMS error:" + ex.getMessage());
	    ex.printStackTrace();
	} finally {
	    DBConnectionUtil.close(null, null, con);
	}
	return list;
    }

}
